package com.fisglobal.ewire;

import java.io.*;

import jxl.*;
import jxl.read.biff.BiffException;

import java.text.SimpleDateFormat;
import java.util.*;

public class ConvertExcel
{
	private ArrayList tagList = new ArrayList<Tag>();
	private static final String[][] S_AND_T_TAGS =
		{
		{ "[001]", "1", "Template", "0", "", "", "" },
		{ "[002]", "1", "Institution", "", "", "GFT", "" },
		{ "[003]", "1", "Branch", "", "", "001", "" },
		{ "[004]", "1", "", "", "", "A", "" },
		{ "[012]", "1", "", "", "", "2", "" },
		{ "[014]", "1", "Currency", "", "", "USD", "~" },
		{ "[120]", "1", "Fed type", "", "", "10", "~" },
		{ "[120]", "2", "Fed subtype", "", "", "00", "" },
		{ "[121]", "1", "", "", "", "CTR", "" },
		{ "[132]", "1", "", "", "getTodaysDate", "", "" },
		{ "[139]", "1", "", "", "", "", "" },
		{ "[350]", "1", "RECV", "", "", "", "~" },
		{ "[350]", "2", "RECV", "2", "", "", "~" },
		{ "[350]", "3", "RECV", "3", "", "", "" },
		{ "[400]", "1", "IBK", "", "", "", "~" },
		{ "[400]", "2", "IBK", "10", "", "", "~" },
		{ "[400]", "3", "IBK", "11", "", "", "~" },
		{ "[400]", "4", "IBK", "12", "", "", "~" },
		{ "[400]", "5", "IBK", "13", "", "", "~" },
		{ "[400]", "6", "IBK", "14", "", "", "" },
		{ "[410]", "1", "BBK", "", "", "", "~" },
		{ "[410]", "2", "BBK", "15", "", "", "~" },
		{ "[410]", "3", "BBK", "16", "", "", "~" },
		{ "[410]", "4", "BBK", "17", "", "", "~" },
		{ "[410]", "5", "BBK", "18", "", "", "~" },
		{ "[410]", "6", "BBK", "19", "", "", "" },
		{ "[420]", "1", "BNF", "", "", "", "~" },
		{ "[420]", "2", "BNF", "20", "", "", "~" },
		{ "[420]", "3", "BNF", "21", "", "", "~" },
		{ "[420]", "4", "BNF", "22", "", "", "~" },
		{ "[420]", "5", "BNF", "23", "", "", "~" },
		{ "[420]", "6", "BNF", "24", "", "", "" },
		{ "[500]", "1", "ORG", "", "", "", "~" },
		{ "[500]", "2", "ORG", "4", "", "", "~" },
		{ "[500]", "3", "ORG", "5", "", "", "~" },
		{ "[500]", "4", "ORG", "6", "", "", "~" },
		{ "[500]", "5", "ORG", "7", "", "", "~" },
		{ "[500]", "6", "ORG", "8", "", "", "" },
		{ "[600]", "1", "OBI", "25", "getObi1", "", "~" },
		{ "[600]", "2", "OBI", "25", "getObi2", "", "~" },
		{ "[600]", "3", "OBI", "25", "getObi3", "", "~" },
		{ "[600]", "4", "OBI", "25", "getObi4", "", "" },
		{ "[650]", "1", "BBI", "26", "", "", "~" },
		{ "[650]", "2", "BBI", "26", "", "", "~" },
		{ "[650]", "3", "BBI", "26", "", "", "~" },
		{ "[650]", "4", "BBI", "26", "", "", "~" },
		{ "[650]", "5", "BBI", "26", "", "", "~" },
		{ "[650]", "6", "BBI", "26", "", "", "" } };

	public static void main(String[] args)
	{
		String inputFileName = "z:\\template.xls";
		String outputFileName = "z:\\output.csv";

		if (args.length > 0)
		{
			inputFileName = args[0];
			outputFileName = args[1];
		}
		
		try
		{
			ConvertExcel convert = new ConvertExcel();
			convert.populateTagList();
			convert.printTagList();
			convert.convert(inputFileName, outputFileName);
		}
		catch (UnsupportedEncodingException e)
		{
			debug(e.getMessage());
			e.printStackTrace();
		}
		catch (IOException e)
		{
			debug(e.getMessage());
			e.printStackTrace();
		}
		catch (Exception e)
		{
			debug(e.getMessage());
			e.printStackTrace();
		}
	}

	private void convert(String fromFile, String toFile) throws BiffException, IOException
	{
		//File to store data in form of CSV
		File f = new File(toFile);

		OutputStream os = (OutputStream) new FileOutputStream(f);
		String encoding = "UTF8";
		OutputStreamWriter osw = new OutputStreamWriter(os, encoding);
		BufferedWriter bw = new BufferedWriter(osw);

		//Excel document to be imported
		String filename = fromFile;
		WorkbookSettings ws = new WorkbookSettings();
		ws.setLocale(new Locale("en", "EN"));
		Workbook w = Workbook.getWorkbook(new File(filename), ws);

		int workbookIndex = 0;
		for (int sheet = 0; sheet < w.getNumberOfSheets(); sheet++)
		{
			Sheet s = w.getSheet(sheet);
			if (s.getName().toLowerCase().indexOf("input") >= 0)
			{
				workbookIndex = sheet;
			}
		}

		Sheet sh = w.getSheet(workbookIndex);
		//Gets the cells from sheet
		for (int i = 1; i < sh.getRows(); i++)
		{
			Cell[] row = sh.getRow(i);
			Tag tag = null;
			ArrayList list = (ArrayList) this.tagList.clone();
			for (int x = 0; x < list.size(); x++)
			{
				tag = (Tag) list.get(x);
				tag = (Tag) tag.clone();
				ArrayList<Element> elements = tag.getElement();
				for (int y = 0; y < elements.size(); y++)
				{
					Element element = elements.get(y);
					int index = element.getIndex();
					String value = "";
					String functionName = element.getFunctionName();
					
					if (!isEmpty(functionName))
					{
						String temp = "";
						if (index >= 0 && index < row.length)
						{
							temp = row[index].getContents();
						}
						value = getValue(element, temp);
					}
					else
					{
						if (index >= 0 && index < row.length)
						{
							value = row[index].getContents();
						}
					}
					
					element.setValue(value);
				}

				bw.write(tag.toString());
			}
			bw.newLine();
		}

		bw.flush();
		bw.close();
	}
	
	public static void debug(String msg)
	{
		System.out.println(msg);
	}
	
	private String getValue(Element element, String content)
	{
		String value = "";
		String functionName = element.getFunctionName();
		
		debug("Getting function by name	: " + functionName);
		
		if ("gettodaysdate".equalsIgnoreCase(functionName))
		{
			value = getTodaysDate();
		}
		else if ("getobi1".equalsIgnoreCase(functionName))
		{
			value = getObi(element, 1, content);
		}
		else if ("getobi2".equalsIgnoreCase(functionName))
		{
			value = getObi(element, 2, content);
		}
		else if ("getobi3".equalsIgnoreCase(functionName))
		{
			value = getObi(element, 3, content);
		}
		else if ("getobi4".equalsIgnoreCase(functionName))
		{
			value = getObi(element, 4, content);
		}
		else
		{
			value = "default";
		}
		
		return value;
	}
	
	private String getObi(Element element, int index, String content)
	{
		String value = "";
		String obi = content;
		if (isEmpty(obi))
		{
			return value;
		}
		int startIndex = (35 * (index - 1));
		if (startIndex >= obi.length())
		{
			return value;
		}
		
		int endIndex = 35 * index;
		if (endIndex >= obi.length())
		{
			endIndex = obi.length();
		}
		
		value = obi.substring(startIndex, endIndex);
		debug("obi: (" + obi + "), start:  " + startIndex + ", end:" + endIndex + ", value:(" + value + ")");
		return value;
	}
	
	private void populateTagList()
	{
		populateTagList(tagList);
	}

	private void populateTagList(ArrayList<Tag> list)
	{
		String[][] elementArray = S_AND_T_TAGS.clone();

		String previousTag = "";
		Tag tag = null;
		for (int x = 0; x < elementArray.length; x++)
		{
			String[] elements = elementArray[x];
			String tagName = elements[0];
			int index = -1;
			if (!ConvertExcel.isEmpty(elements[3]))
			{
				String indexString = elements[3];
				index = Integer.parseInt(indexString);
			}

			if (x == 0)
			{
				Element element = new Element(index, elements[4], elements[5], elements[6]);
				tag = new Tag(tagName);
				tag.addElement(element);
				previousTag = tagName;
				continue;
			}

			Element element = new Element(index, elements[4], elements[5], elements[6]);
			if (!tagName.equalsIgnoreCase(previousTag))
			{
				list.add(tag);
				tag = new Tag(tagName);
			}

			tag.addElement(element);
			previousTag = tagName;
		}
		list.add(tag);
	}

	public void printTagList()
	{
		for (int x = 0; x < this.tagList.size(); x++)
		{
			debug("" + tagList.get(x));
		}
	}

	private static boolean isEmpty(String s)
	{
		if (s == null)
		{
			return true;
		}

		if (s.trim().length() == 0)
		{
			return true;
		}

		return false;
	}
	
	private String getTodaysDate()
	{
		SimpleDateFormat sdm = new SimpleDateFormat("yyyyMMdd");
		Calendar calendar = Calendar.getInstance();
		String date = sdm.format(calendar.getTime()).toUpperCase();
		return date;
	}

	
}
